package com.xyy.ec.qualification.utils;

import com.alibaba.fastjson.JSON;
import com.google.common.collect.Lists;
import com.xyy.ec.qualification.config.ConfigManagerService;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.function.Function;

public class SQLExecuteUtil {
    private static final Logger logger = LoggerFactory.getLogger(SQLExecuteUtil.class);

    /**
     * SQL执行
     *
     * @param ds
     * @param sql
     * @param values
     * @param fun
     * @return
     */
    public static Object sqlRS(DataSource ds, String sql, List<Object> values, Function<ResultSet, Object> fun) {
        try (Connection conn = ds.getConnection()) {
            try (PreparedStatement pstmt = conn
                    .prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
                if (values != null) {
                    for (int i = 0; i < values.size(); i++) {
                        pstmt.setObject(i + 1, values.get(i));
                    }
                }

                try (ResultSet rs = pstmt.executeQuery()) {
                    return fun.apply(rs);
                }
            }
        } catch (Exception e) {
            logger.error("sqlRs has error, sql: " + sql + ", error msg：" + e.getMessage(), e);
            throw new RuntimeException(e);
        }
    }

    /**
     * SQL执行
     *
     * @param ds
     * @param values
     * @param
     * @return
     */
    public static Boolean sqlExecuteUpdate(DataSource ds, String sql, List<Object> values) {
        Connection connection = null;
        try {
            connection = ds.getConnection();
            connection.setAutoCommit(false);
            try (PreparedStatement statement = connection.prepareStatement(sql)) {
                if (values != null) {
                    for (int i = 0; i < values.size(); i++) {
                        statement.setObject(i + 1, values.get(i));
                    }
                }
                logger.warn("药帮忙客户端执行sql语句:{}", getSql(sql, values));
                statement.executeUpdate();
                connection.commit();
            }
            return Boolean.TRUE;
        } catch (Exception e) {
            try {
                if (connection != null) {
                    connection.rollback();
                }
            } catch (SQLException throwables) {
                logger.error("sqlExecuteUpdate has error, sql: " + sql + ", error msg：" + throwables.getMessage(), throwables);
            }
            logger.error("sqlExecuteUpdate has error, sql: " + sql + ", error msg：" + e.getMessage(), e);
        } finally {
            try {
                if (connection != null) connection.close();
            } catch (Exception e) {
            }
        }
        return Boolean.FALSE;
    }

    /**
     * SQL执行
     *
     * @param ds
     * @param sqlList
     * @param sqlValues
     * @return
     */
    public static Boolean sqlListExecuteUpdate(DataSource ds, List<String> sqlList, List<List<Object>> sqlValues) {
        Connection connection = null;
        ArrayList<PreparedStatement> arrayList = Lists.newArrayList();
        try {
            connection = ds.getConnection();
            connection.setAutoCommit(false);
            for (int i = 0; i < sqlList.size(); i++) {
                PreparedStatement statement = connection.prepareStatement(sqlList.get(i));
                if (CollectionUtils.isNotEmpty(sqlValues)) {
                    List<Object> values = sqlValues.get(i);
                    for (int j = 0; j < values.size(); j++) {
                        Object val = values.get(j);
                        //logger.debug(j + ":预绑定sql " + sqlList.get(i) + ":" + val  );
                        if (val instanceof Date) {
                            val = new java.sql.Timestamp(((Date) val).getTime());
                        }
                        statement.setObject(j + 1, val);
                    }
                }
                arrayList.add(statement);
            }
            for (int i = 0; i < arrayList.size(); i++) {
                PreparedStatement statement = arrayList.get(i);
                //执行的sql
                logger.warn("药帮忙客户端执行sql语句:{}", getSql(sqlList.get(i), sqlValues.get(i)));
                statement.executeUpdate();
            }
            connection.commit();
            return Boolean.TRUE;
        } catch (Exception e) {
            try {

                if (connection != null) {
                    connection.rollback();
                }
            } catch (SQLException throwables) {
                logger.error("sqlListExecuteUpdate has error, sql: " + sqlList + ", error msg：" + throwables.getMessage(), throwables);
            }
            logger.error("sqlListExecuteUpdate has error, sql: " + sqlList + ", error msg：" + e.getMessage(), e);
        } finally {
            try {
                if (CollectionUtils.isNotEmpty(arrayList)) {
                    for (PreparedStatement statement : arrayList) {
                        if (statement != null) {
                            statement.close();
                        }
                    }
                }
            } catch (Exception e) {
            }
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (Exception e) {
            }
        }
        return Boolean.FALSE;
    }

    public static String getSql(String sql, List<Object> values) {
        try {
            if (StringUtils.isBlank(sql)) {
                return StringUtils.EMPTY;
            } else if (CollectionUtils.isEmpty(values)) {
                return sql;
            }

            int matches = StringUtils.countMatches(sql, "?");

            if (matches != values.size()) {
                logger.error("SQLExecuteUtil.getSql占位符和值数量不配置，请升级一下对接工具");
                return StringUtils.EMPTY;
            }
            for (Object o : values) {

                if (o == null) {
                    sql = sql.replaceFirst("\\?", "null");
                } else if (o instanceof String) {
                    sql = sql.replaceFirst("\\?", "'" + (String) o + "'");
                } else if (o instanceof Date) {
                    sql = sql.replaceFirst("\\?", "'" + DateFormatUtils.format((Date) o, "yyyy-MM-dd HH:mm:ss") + "'");
                } else if (NumberUtils.isNumber(String.valueOf(o))) {
                    sql = sql.replaceFirst("\\?", String.valueOf(o));
                }
            }

            return sql;
        } catch (Exception e) {
            logger.error("SQLExecuteUtil.getSql异常， params sql:{},values:{}", sql, JSON.toJSONString(values));
            return StringUtils.EMPTY;
        }
    }


    /**
     * 获取Count
     *
     * @return 客户数量
     */
    public static Integer getCount(String sql) {
        return (Integer) SQLExecuteUtil.sqlRS(ConfigManagerService.getDataSource(), sql, null, rs -> {
            Integer count = null;
            try {
                if (rs.next()) {
                    count = ((Number) rs.getObject(NumberUtils.INTEGER_ONE)).intValue();
                }
            } catch (Exception e) {
                logger.error(e.getMessage(), e);
            }
            return count == null ? NumberUtils.INTEGER_ZERO : count;
        });
    }
}
